
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.net.HttpURLConnection;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JButton;
import javax.swing.JComboBox;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JOptionPane;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;

import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/**
 *
 * @author MJ
 */
public class OlapParser1 extends JFrame implements ActionListener {

    /**
     * @param args the command line arguments
     */
    JLabel header = new JLabel("Wybierz źródło danych:");
    JComboBox choose = new JComboBox();
    JButton work = new JButton("Pobierz");
    
    public OlapParser1(){
        
        //dodanie pozycji do combo boxa
        choose.addItem("http://dziennikmz.mz.gov.pl/DUM_MZ/2012/6/oryginal/Zalacznik1.xls");
        choose.addItem("http://www.mz.gov.pl/__data/assets/excel_doc/0019/7057/4obwieszczenie_llr_23122013.xls");
        choose.addItem("http://www.certus.med.pl/include/user_file/lista_lek%C3%B3w_refundowanych_24042013.xls");
        choose.addItem("http://www.izba-lekarska.org.pl/files/media_files/lista%20lekow%20refundowanych%20w%20chorobach%20przewleklych.xls");
        choose.addItem("http://www.radlinski.pl/attachments/article/72/lista-lekow-2014.xls");
        
        this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        this.setSize(500, 500);
        this.setTitle("OLAP - Parser");
        
        work.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
            URL page;
                try {
                    String name=choose.getSelectedItem().toString();
                    
                    page = new URL(name);
                    HttpURLConnection conn = (HttpURLConnection) page.openConnection();
                    conn.connect();
                    InputStream input = conn.getInputStream();
                    
                    HSSFWorkbook workbook = new HSSFWorkbook(input);
             
                    HSSFSheet sheet = workbook.getSheetAt(0);
                    ArrayList<Lek> leki = new ArrayList<>();
                    
                    boolean check = false;
                    
                    // 1 zrodlo
                    if(name.equalsIgnoreCase("http://dziennikmz.mz.gov.pl/DUM_MZ/2012/6/oryginal/Zalacznik1.xls")){
                        Iterator<Row> rowIterator = sheet.iterator();
                    
                        while(rowIterator.hasNext() && check == false) {
                            Row row = rowIterator.next();
                            if(row.getRowNum()<3 || row.getRowNum()>10822){
                                continue;
                            }

                            //For each row, iterate through each columns
                            Iterator<Cell> cellIterator = row.cellIterator();
                            String nazwa="";
                            String subst="";
                            String dawka="";
                            String postac="";
                            while(cellIterator.hasNext()) {
                                
                                
                                Cell cell = cellIterator.next();
                                if(cell.getColumnIndex()<0 || cell.getColumnIndex()>4){
                                    continue;
                                }

                                switch(cell.getCellType()) {
                                    case Cell.CELL_TYPE_BOOLEAN:
                                        

                                        break;
                                    case Cell.CELL_TYPE_NUMERIC:
                                        
                                        break;
                                    case Cell.CELL_TYPE_STRING:
                                        if(cell.getColumnIndex()==1 && !cell.getStringCellValue().isEmpty()){
                                           nazwa=cell.getStringCellValue();
                                           //System.out.println(nazwa);
                                        }
                                        else if(cell.getColumnIndex()==2 && !cell.getStringCellValue().isEmpty()){
                                           subst=cell.getStringCellValue();
                                           //System.out.println(subst);
                                        }
                                        else if(cell.getColumnIndex()==3 && !cell.getStringCellValue().isEmpty()){
                                           postac=cell.getStringCellValue();
                                           //System.out.println(postac);
                                        }
                                        else if(cell.getColumnIndex()==4 && !cell.getStringCellValue().isEmpty()){
                                           dawka=cell.getStringCellValue();
                                           //System.out.println(dawka);
                                           Lek lek=new Lek(nazwa,subst, postac,dawka);
                                           leki.add(lek);
                                        }

                                        break;
                                }
                            }
                            
                        }
                        saveData(leki); // zapis do excela
//                      for(int i=0;i<leki.size();i++){
//                          System.out.println(leki.get(i).nazwa+" "+leki.get(i).postac+leki.get(i).dawka);
//                      }  
                    }
                    
                    // 2
                    if(name.equalsIgnoreCase("http://www.mz.gov.pl/__data/assets/excel_doc/0019/7057/4obwieszczenie_llr_23122013.xls")){
                        Iterator<Row> rowIterator = sheet.iterator();
                    
                        while(rowIterator.hasNext() && check == false) {
                            Row row = rowIterator.next();
                            if(row.getRowNum()<3){
                                continue;
                            }

                            //For each row, iterate through each columns
                            Iterator<Cell> cellIterator = row.cellIterator();
                            String nazwa="";
                            String subst="";
                            String dawka="";
                            String postac="";

                            while(cellIterator.hasNext()) {
                                Cell cell = cellIterator.next();
                                if(cell.getColumnIndex()<0 || cell.getColumnIndex()>2){
                                    continue;
                                }

                                switch(cell.getCellType()) {
                                    case Cell.CELL_TYPE_BOOLEAN:
                                        

                                        break;
                                    case Cell.CELL_TYPE_NUMERIC:
                                        
                                        break;
                                    case Cell.CELL_TYPE_STRING:
                                    if(cell.getColumnIndex()==1 && !cell.getStringCellValue().isEmpty()){
                                           subst=cell.getStringCellValue();
                                           //System.out.println(subst);
                                        }
                                        else if(cell.getColumnIndex()==2 && !cell.getStringCellValue().isEmpty()){
                                           String[] parts = cell.getStringCellValue().split(", ");
                                           nazwa=parts[0];
                                           postac=parts[1];
                                           dawka=parts[2];
                                           //System.out.println(subst);
                                           Lek lek=new Lek(nazwa,subst, postac,dawka);
                                           leki.add(lek);
                                        }                
                                        

                                        break;
                                }
                            }
                            
                        }
                        saveData(leki); // zapis do excela
//                      for(int i=0;i<leki.size();i++){
//                          System.out.println(leki.get(i).nazwa+" "+leki.get(i).postac+leki.get(i).dawka);
//                      }  
                    }

                  // 3
                 if(name.equalsIgnoreCase("http://www.certus.med.pl/include/user_file/lista_lek%C3%B3w_refundowanych_24042013.xls")){
                        Iterator<Row> rowIterator = sheet.iterator();
                    
                        while(rowIterator.hasNext() && check == false) {
                            Row row = rowIterator.next();
                            if(row.getRowNum()<3 || row.getRowNum()>10822){
                                continue;
                            }

                            //For each row, iterate through each columns
                            Iterator<Cell> cellIterator = row.cellIterator();
                            String nazwa="";
                            String subst="";
                            String dawka="";
                            String postac="";


                            while(cellIterator.hasNext()) {
                                Cell cell = cellIterator.next();
                                if(cell.getColumnIndex()<0 || cell.getColumnIndex()>3){
                                    continue;
                                }

                                switch(cell.getCellType()) {
                                    case Cell.CELL_TYPE_BOOLEAN:
                                        

                                        break;
                                    case Cell.CELL_TYPE_NUMERIC:
                                       
                                        break;
                                    case Cell.CELL_TYPE_STRING:
                                        if(cell.getColumnIndex()==1 && !cell.getStringCellValue().isEmpty()){
                                           subst=cell.getStringCellValue();
                                           //System.out.println(subst);
                                        }
                                        else if(cell.getColumnIndex()==2 && !cell.getStringCellValue().isEmpty()){
                                           String[] parts = cell.getStringCellValue().split(", ");
                                           nazwa=parts[0];
                                           postac=parts[1];
                                           dawka=parts[2];
                                           //System.out.println(subst);
                                           Lek lek=new Lek(nazwa,subst, postac,dawka);
                                           leki.add(lek);
                                        }          

                                        break;
                                }
                            }
                            
                        }
                        saveData(leki); // zapis do excela
//                      for(int i=0;i<leki.size();i++){
//                          System.out.println(leki.get(i).nazwa+" "+leki.get(i).postac+leki.get(i).dawka);
//                      }  
                    }   
                 
                 // 4
                 if(name.equalsIgnoreCase("http://www.izba-lekarska.org.pl/files/media_files/lista%20lekow%20refundowanych%20w%20chorobach%20przewleklych.xls")){
                        Iterator<Row> rowIterator = sheet.iterator();
                    
                        while(rowIterator.hasNext() && check == false) {
                            Row row = rowIterator.next();
                            if(row.getRowNum()<3 || row.getRowNum()>2691){
                                continue;
                            }

                            //For each row, iterate through each columns
                            Iterator<Cell> cellIterator = row.cellIterator();
                            String nazwa="";
                            String subst="";
                            String dawka="";
                            String postac="";

                            while(cellIterator.hasNext()) {
                                Cell cell = cellIterator.next();
                                if(cell.getColumnIndex()<0 || cell.getColumnIndex()>3){
                                    continue;
                                }

                                switch(cell.getCellType()) {
                                    case Cell.CELL_TYPE_BOOLEAN:
                                        

                                        break;
                                    case Cell.CELL_TYPE_NUMERIC:
                                        
                                        break;
                                    case Cell.CELL_TYPE_STRING:
                                        if(cell.getColumnIndex()==1 && !cell.getStringCellValue().isEmpty()){
                                           subst=cell.getStringCellValue();
                                           //System.out.println(subst);
                                        }
                                        else if(cell.getColumnIndex()==2 && !cell.getStringCellValue().isEmpty()){
                                           String[] parts = cell.getStringCellValue().split(", ");
                                           nazwa=parts[0];
                                           postac=parts[1];
                                           dawka=parts[2];
                                           //System.out.println(subst);
                                           Lek lek=new Lek(nazwa,subst, postac,dawka);
                                           leki.add(lek);
                                        }          

                                        break;
                                }
                            }
                         
                        }
                      saveData(leki); // zapis do excela
//                      for(int i=0;i<leki.size();i++){
//                          System.out.println(leki.get(i).nazwa+" "+leki.get(i).postac+leki.get(i).dawka);
//                      }     
                    } 
                 
                 //5
                 if(name.equalsIgnoreCase("http://www.radlinski.pl/attachments/article/72/lista-lekow-2014.xls")){
                        Iterator<Row> rowIterator = sheet.iterator();
                    
                        while(rowIterator.hasNext() && check == false) {
                            Row row = rowIterator.next();
                            if(row.getRowNum()<3 || row.getRowNum()>10822){
                                continue;
                            }

                            //For each row, iterate through each columns
                            Iterator<Cell> cellIterator = row.cellIterator();
                            String nazwa="";
                            String subst="";
                            String dawka="";
                            String postac="";

                            while(cellIterator.hasNext()) {
                                Cell cell = cellIterator.next();
                                if(cell.getColumnIndex()<0 || cell.getColumnIndex()>3){
                                    continue;
                                }

                                switch(cell.getCellType()) {
                                    case Cell.CELL_TYPE_BOOLEAN:
                                        

                                        break;
                                    case Cell.CELL_TYPE_NUMERIC:
                                        
                                        break;
                                    case Cell.CELL_TYPE_STRING:
                                        if(cell.getColumnIndex()==1 && !cell.getStringCellValue().isEmpty()){
                                           subst=cell.getStringCellValue();
                                           //System.out.println(subst);
                                        }
                                        else if(cell.getColumnIndex()==2 && !cell.getStringCellValue().isEmpty()){
                                           String[] parts = cell.getStringCellValue().split(", ");
                                           nazwa=parts[0];
                                           postac=parts[1];
                                           dawka=parts[2];
                                           //System.out.println(subst);
                                           Lek lek=new Lek(nazwa,subst, postac,dawka);
                                           leki.add(lek);
                                        }   

                                        break;
                                }
                            }
                            
                        }
                       saveData(leki); // zapis do excela
//                      for(int i=0;i<leki.size();i++){
//                          System.out.println(leki.get(i).nazwa+" "+leki.get(i).postac+leki.get(i).dawka);
//                      }    
                    }    
                    

                JOptionPane.showMessageDialog(null,"Dane zostały pobrane!");    
                } catch (MalformedURLException ex) {
                    Logger.getLogger(OlapParser1.class.getName()).log(Level.SEVERE, null, ex);
                } catch (IOException ex) {
                    Logger.getLogger(OlapParser1.class.getName()).log(Level.SEVERE, null, ex);
                }
                 
            }
             
        });
        GridLayout layout = new GridLayout(0,1);
        this.setLayout(layout);
        this.getContentPane().add(header);
        this.getContentPane().add(choose);
        this.getContentPane().add(new JLabel(""));
        this.getContentPane().add(work);
        this.setLocationRelativeTo(null);
        this.pack();
        this.setVisible(true);
    }
    public static void main(String[] args) {
        new OlapParser1();
    }

    @Override
    public void actionPerformed(ActionEvent e) {
        throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.
    }
    
    public void saveData(ArrayList<Lek> leki){
         
        try {
			FileOutputStream fileOut = new FileOutputStream("leki.xls");
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet worksheet = workbook.createSheet("POI Worksheet");

			// index from 0,0... cell A1 is cell(0,0)
			HSSFRow row1 = worksheet.createRow((short) 0);

			HSSFCell cellA1 = row1.createCell((short) 0);
			cellA1.setCellValue("Nazwa");
                      
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			
			cellA1.setCellStyle(cellStyle);

			HSSFCell cellB1 = row1.createCell((short) 1);
			cellB1.setCellValue("Substancja czynna");
			cellStyle = workbook.createCellStyle();
			
			cellB1.setCellStyle(cellStyle);

			HSSFCell cellC1 = row1.createCell((short) 2);
			cellC1.setCellValue("Postać");
			cellStyle = workbook.createCellStyle();
			
			cellC1.setCellStyle(cellStyle);

			HSSFCell cellD1 = row1.createCell((short) 3);
			cellD1.setCellValue("Dawka");
			cellStyle = workbook.createCellStyle();
			
			cellD1.setCellStyle(cellStyle);
                        
                        for(int i=0;i<leki.size();i++){
                            HSSFRow row = worksheet.createRow((short) i+1);
                            HSSFCell cell0 = row.createCell((short) 0);
                            cell0.setCellValue(leki.get(i).nazwa);
                            
                            HSSFCell cell1 = row.createCell((short) 1);
                            cell1.setCellValue(leki.get(i).subst);
                            
                            HSSFCell cell2 = row.createCell((short) 2);
                            cell2.setCellValue(leki.get(i).postac);
                            
                            HSSFCell cell3 = row.createCell((short) 3);
                            cell3.setCellValue(leki.get(i).dawka);
			    
                        }

			workbook.write(fileOut);
			fileOut.flush();
			fileOut.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

    }
}
